#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""
@File Name  : c_mysql.py
@Author     : LeeCQ
@Date-Time  : 2019/12/3 16:11

"""
import pymysql


class MySQL9C:
    """
    关于9C的数据库操作；
    """
    SQL_HOST = 'a.leecq.xyz'
    SQL_PORT = 3306
    SQL_USER = 'CMS9C'
    SQL_PASSWD = '123456'
    SQL_DB = 'CMS9C'
    SQL_CHARSET = 'utf8'

    TABLE_PREFIX = 'c9'

    def __init__(self):
        self._sql = pymysql.connect(host=self.SQL_HOST,
                                    port=self.SQL_PORT,
                                    user=self.SQL_USER,
                                    password=self.SQL_PASSWD,  # 可以用 passwd为别名
                                    database=self.SQL_DB,  # 可以用 db    为别名；
                                    charset=self.SQL_CHARSET
                                    )

    def connect_test(self):
        cur = self._sql.cursor()
        print(cur.fetchone())

    def _write_db(self, command, *args):
        """执行数据库写入操作"""
        cur = self._sql.cursor()  # 使用cursor()方法获取操作游标
        cur.execute(command, *args)
        # if args: cur.execute(command, args)  # 使用execute方法执行SQL语句
        # else: cur.execute(command)
        self._sql.commit()  # 提交数据库
        cur.close()  # 释放游标
        return 0

    def _read_db(self):
        pass

    def _create_table(self, command):
        return self._write_db(command)

    def _append(self, table, **kwargs):
        _c = (f"INSERT INTO `{self.TABLE_PREFIX}_{table}`  "
              "( " + ', '.join(["`" + _k + "`" for _k in kwargs.keys()]) + " ) "
                                                                           " VALUES "
                                                                           " ( " + ', '.join(
            [" %s " for _k in kwargs.values()]) + " ) ; "
              )
        return self._write_db(_c, list(kwargs.values()))

    def _update(self):
        pass

    def _delete(self):
        pass

    def book_create(self):
        """c9_book"""
        _c = (f"CREATE TABLE IF NOT EXISTS `{self.TABLE_PREFIX}_book`  ("
              "a_id         INT             UNIQUE, "
              "a_name       VARCHAR(100), "
              "a_pic        MEDIUMBLOB, "
              "a_remarks    TEXT, "
              "a_type       INT, "
              "a_time       DATETIME"  # 注意最后一个参数后面不要有 逗号；
              ")")
        return self._create_table(_c)

    def book_append(self, a_id, a_name, a_pic, a_remarks, a_type, a_time):
        """插入数据库 -> 'a_id', 'a_name', 'a_pic', 'a_remarks', 'a_type', 'a_time'"""
        self.book_create()
        return self._append('book',
                            a_id=a_id,
                            a_name=a_name,
                            a_pic=a_pic,
                            a_remarks=a_remarks,
                            a_type=a_type,
                            a_time=a_time
                            )

    def pic_create(self):
        _c = (f"CREATE TABLE IF NOT EXISTS `{self.TABLE_PREFIX}_pic`  ("
              "a_id         INT             UNIQUE, "
              "a_name       VARCHAR(100), "
              "a_pic        MEDIUMBLOB, "
              "a_remarks    TEXT, "
              "a_type       INT, "
              "a_time       DATETIME"  # 注意最后一个参数后面不要有 逗号；
              ")")
        return self._create_table(_c)

    def pic_append(self, a_id, a_name, a_pic, a_remarks, a_type, a_time):
        """插入数据库 -> 'a_id', 'a_name', 'a_pic', 'a_remarks', 'a_type', 'a_time'"""
        self.pic_create()
        return self._append('pic',
                            a_id=a_id,
                            a_name=a_name,
                            a_pic=a_pic,
                            a_remarks=a_remarks,
                            a_type=a_type,
                            a_time=a_time
                            )

    def TEST_APPEND(self):
        self._create_table('CREATE TABLE IF NOT EXISTS c9_test (a_id INT)')
        self._append('test', a_id='123')
        return 0


if __name__ == '__main__':
    pass

